cd
set more off
use "PHL_Survey_Data.dta", clear
gen treatment=0 if Q1_3_Villagecode==1| Q1_3_Villagecode==4| Q1_3_Villagecode==7| Q1_3_Villagecode==8| Q1_3_Villagecode==11| Q1_3_Villagecode==13| Q1_3_Villagecode==16| Q1_3_Villagecode==18| Q1_3_Villagecode==19
replace treatment=1 if Q1_3_Villagecode==2| Q1_3_Villagecode==6| Q1_3_Villagecode==14| Q1_3_Villagecode==15| Q1_3_Villagecode==17| Q1_3_Villagecode==20
replace treatment=2 if Q1_3_Villagecode==3| Q1_3_Villagecode==5| Q1_3_Villagecode==9| Q1_3_Villagecode==10| Q1_3_Villagecode==12| Q1_3_Villagecode==21

rename Q1_3_Villagecode villagecode
rename Q1_5_Household hhid

gen hhsize= Q2_10E_M+ Q2_10E_F
gen incompleteprimary=(Q2_7A_Education<17)
gen completeprimary=(Q2_7A_Education==17| Q2_7A_Education==18)
gen aboveprimary=(Q2_7A_Education>18)
gen activeworkers= Q2_12E_M+ Q2_12E_F
rename Q2_2A_Sex sex
rename Q2_3A_Age age

rename Q2_22_DistanceRoad timetoroad
rename Q2_21_DistanceMkt timetomkt
rename Q2_23_MaizeExperience maizeexperienceyears

rename Q4_3A agriclandarea
rename Q4_4A maizelandarea
rename Q4_5A maizeplots
rename Q4_30 priceharvestseason
rename Q4_31 priceleanseason

replace agriclandarea=0.49*agriclandarea
replace maizelandarea=0.49*maizelandarea

gen schooling=0 if Q2_7A_Education==0
replace schooling =1 if Q2_7A_Education==1
replace schooling =1 if Q2_7A_Education==2
replace schooling =1 if Q2_7A_Education==11
replace schooling =2 if Q2_7A_Education==12
replace schooling =3 if Q2_7A_Education==13
replace schooling =4 if Q2_7A_Education==14
replace schooling =5 if Q2_7A_Education==15
replace schooling =6 if Q2_7A_Education==16
replace schooling =7 if Q2_7A_Education==17
replace schooling =8 if Q2_7A_Education==18
replace schooling =9 if Q2_7A_Education==19
replace schooling =9 if Q2_7A_Education==21
replace schooling =10 if Q2_7A_Education==22
replace schooling =11 if Q2_7A_Education==23
replace schooling =12 if Q2_7A_Education==24
replace schooling =13 if Q2_7A_Education==25
replace schooling =13 if Q2_7A_Education==31
replace schooling =14 if Q2_7A_Education==32
replace schooling =15 if Q2_7A_Education==33
replace schooling =16 if Q2_7A_Education==34
replace schooling =16 if Q2_7A_Education==41
replace schooling =17 if Q2_7A_Education==42
replace schooling =18 if Q2_7A_Education==43
replace schooling =19 if Q2_7A_Education==44
replace schooling =20 if Q2_7A_Education==45

gen hybridprop=Q5_7a_Largest/Q5_3_Largest

gen harvestmatured=(Q5_23_Largest==2)  if Q5_23_Largest!=.
gen weatheratharvest=(Q5_24_Largest==3)  if Q5_24_Largest!=. & included==1

gen immediatestorage=( Q5_28==1) /*spread instead of being heaped or put in bags*/
gen dried=(Q5_30==1| Q5_33==1)
gen dryingperiod= Q5_35
replace dryingperiod=0 if dried!=. & Q5_35==.
gen dryingperiodsq=(dryingperiod)^2
egen amountstored=rowtotal (Q5_45A-Q5_45D)
replace amountstored=. if amountstored==0
gen logamountstored=ln(amountstored)
gen propstoredhome= Q5_45A/( Q5_45A+Q5_45B+Q5_45C+Q5_45D)
gen propstoredfarm= Q5_45B/( Q5_45A+Q5_45B+Q5_45C+Q5_45D)
gen propstoredcommmunityfac= Q5_45C/( Q5_45A+Q5_45B+Q5_45C+Q5_45D)
gen propstoredother= Q5_45D/( Q5_45A+Q5_45B+Q5_45C+Q5_45D)
gen propstored4food= Q5_46A/(Q5_46A+Q5_46B+Q5_46C)
gen propstored4sale= Q5_46B/(Q5_46A+Q5_46B+Q5_46C)
gen propstored4other= Q5_46C/(Q5_46A+Q5_46B+Q5_46C)
gen sack=(Q5_47==4)
gen traditionalstorage=(Q5_47==1| Q5_47==8| Q5_47==2)
gen modernstorage=(Q5_47==3| Q5_47==5)

gen amountharvested= Q5_65A+ Q5_65B+ Q5_65C+ Q5_65D /*generate total amount harvested from all plots*/
gen logamountharvested= ln(amountharvested)
gen mashineshell=Q5_65A/amountharvested
gen beatingshell=Q5_65B/amountharvested 
gen handshell=Q5_65C/amountharvested

gen alloted4sale=amountharvested- Q5_46A- Q5_46C-Q5_38
gen propsoldin3months= Q5_72/ amountharvested
replace propsoldin3months=0 if propsoldin3months==. & amountharvested!=.

gen Spraying=(Q5_51A==1) if amountharvested!=.  & included==1
gen Smoking=(Q5_51B==1) if amountharvested!=.  & included==1
gen Trappoison=(Q5_51C==1) if amountharvested!=.  & included==1

gen protcostperton=Q5_52*1000/(2100*amountstored)

replace Q5_73=Q5_73/2100
replace Q5_74=Q5_74/2100

gen salesloss= Q5_87
replace salesloss=0 if Q5_86==0
gen prestoragelossprop=Q5_38/amountharvested
gen storagelossprop= Q5_55/ amountstored
gen saleslossprop=salesloss/Q5_71

gen yield=amountharvested/maizelandarea

egen wealth=rowtotal( Q7_1_Value Q7_2_Value Q7_3_Value Q7_4_Value Q7_5_Value Q7_6_Value Q7_7_Value Q7_8_Value Q7_9_Value Q7_10_Value Q7_11_Value Q7_12_Value Q7_13_Value Q7_14_Value Q7_15_Value Q7_16_Value Q7_17_Value Q7_18_Value Q7_19_Value Q7_20_Value Q7_21_Value Q7_22_Value Q7_23_Value)
gen logwealth=ln(wealth)

egen income=rowtotal(Q8_1b_HH Q8_2b_HH Q8_3b_HH Q8_4b_HH Q8_5b_HH Q8_6b_HH Q8_7b_HH Q8_8b_HH Q8_9b_HH)
********
rename Q5_38 prestorageloss
rename Q5_55 storageloss
rename Q5_29 maizesorted
rename Q5_48 storecleaned
rename Q5_49 storedisinfected
rename Q5_50 usedprotectants
rename Q5_70 soldmaize
rename Q5_71 amountsold
rename Q5_72 amountsoldin3months
rename Q5_73 highestprice
rename Q5_74 lowestprice
rename Q5_75 transactions 
rename Q5_80 transported4sale 
rename Q5_98 trained

gen logsold=ln(amountsold)
replace highestprice=highestprice*1000
replace lowestprice=lowestprice*1000
gen averageprice=0.5*(highestprice+lowestprice)

/*Loss per harvest*/
gen prestolossprop=prestorageloss/amountharvested
gen stolossprop=storageloss/amountharvested
gen selllossprop=salesloss/amountharvested
replace stolossprop=0 if stolossprop==. & amountharvested>0 & amountharvested!=.
/*Total loss*/
gen phl=(salesloss+storageloss+prestorageloss)/amountharvested
replace phl=(storageloss+prestorageloss)/amountharvested if phl==. & salesloss==.
replace phl=(prestorageloss+salesloss)/amountharvested if phl==. & storageloss==.
replace phl=(prestorageloss)/amountharvested if phl==. & storageloss==. & salesloss==.
replace income=income/2100
replace wealth=wealth/2100

gen wave=0
gen control=(treatment==0)
gen bagstreatment=(treatment==1)
gen traintreatment=(treatment==2)

keep hhid villagecode sex age schooling hhsize activeworkers wealth income prestolossprop stolossprop selllossprop phl prestoragelossprop prestorageloss storagelossprop storageloss saleslossprop salesloss highestprice lowestprice averageprice maizeexperienceyears agriclandarea maizelandarea maizeplots hybridprop weatheratharvest  harvestmatured immediatestorage maizesorted dryingperiod handshell mashineshell beatingshell amountharvested logamountharvested amountstored Spraying Smoking Trappoison protcostperton logamountstored propstored4sale propstored4food propstored4other sack traditionalstorage modernstorage storecleaned storedisinfected usedprotectants soldmaize amountsold logsold amountsoldin3months propsoldin3months transactions trained transported4sale treatment control bagstreatment traintreatment wave included
saveold "Drivers of post harvest lossses Final.dta", replace

keep hhid villagecode treatment control bagstreatment traintreatment sex age schooling activeworkers hhsize income wealth maizeexperienceyears trained
saveold "PHL First Round Socieoeconomic.dta", replace

*********************
use "Second round data.dta", clear
drop if Q2_2A_maizelandprev>25 & Q2_2A_maizelandprev<. /*removing one outlier*/
replace Q2_1A_agriclandprev=0.49*Q2_1A_agriclandprev
replace Q2_2A_maizelandprev=0.49*Q2_2A_maizelandprev
gen yield=Q2_9_actual_harvest/Q2_2A_maizelandprev
gen amountstored= Q2_44A_storedhome+Q2_44B_storedfarm+Q2_44C_storedcommunity+Q2_44D_storedotherplace
replace amountstored=. if amountstored==0
gen propsoldin3months=Q2_59_sold3months/Q2_9_actual_harvest
replace propsoldin3months=0 if propsoldin3months==. & Q2_9_actual_harvest !=.
gen weatheratharvest=(Q2_24_weatheratharvest==3) if Q2_9_actual_harvest!=. & Q2_9_actual_harvest!=0
replace Q2_52A_used_chemicals=0 if amountstored!=0 & amountstored!=. & Q2_52A_used_chemicals==.
replace Q2_52B_used_ashesandsmoking=0 if amountstored!=0 & amountstored!=. & Q2_52B_used_ashesandsmoking==.
replace Q2_52C_used_rattrapsandpoison=0 if amountstored!=0 & amountstored!=. & Q2_52C_used_rattrapsandpoison==.
replace Q2_52D_used_cats=0 if amountstored!=. & amountstored!=0 & Q2_52D_used_cats==.
replace Q2_53_costofprotection=0 if amountstored!=. & Q2_53_costofprotection==.

/*Loss per amount harvested*/
gen prestolossprop= Q2_41_preharvest_loss/Q2_9_actual_harvest
gen stolossprop=Q2_56_qty_storageloss/Q2_9_actual_harvest
gen selllossprop= Q2_68_marketing_loss/Q2_9_actual_harvest
replace stolossprop=0 if stolossprop==. & Q2_9_actual_harvest>0 & Q2_9_actual_harvest!=.
replace selllossprop=0 if selllossprop==. & Q2_9_actual_harvest>0 & Q2_9_actual_harvest!=.

/*Loss per amount available at each stage*/
gen prestoragelossprop= Q2_41_preharvest_loss/Q2_9_actual_harvest
gen storagelossprop=Q2_56_qty_storageloss/amountstored
gen saleslossprop= Q2_68_marketing_loss/Q2_58_amont_sold

/*Total loss*/
gen phl=(Q2_68_marketing_loss+Q2_56_qty_storageloss+Q2_41_preharvest_loss)/ Q2_9_actual_harvest
replace phl=(Q2_56_qty_storageloss+Q2_41_preharvest_loss)/ Q2_9_actual_harvest if phl==. &Q2_68_marketing_loss==.
replace phl=(Q2_41_preharvest_loss+Q2_68_marketing_loss)/ Q2_9_actual_harvest if phl==. &Q2_56_qty_storageloss==.
replace phl=(Q2_41_preharvest_loss)/ Q2_9_actual_harvest if phl==. &Q2_56_qty_storageloss==. & Q2_68_marketing_loss==.


gen roundtwo=0 if Q1_1_Household_ID=="028"| Q1_1_Household_ID=="041"| Q1_1_Household_ID=="055"| Q1_1_Household_ID=="090"| Q1_1_Household_ID=="105"| Q1_1_Household_ID=="158"| Q1_1_Household_ID=="172"| Q1_1_Household_ID=="190"| Q1_1_Household_ID=="208"| Q1_1_Household_ID=="216"| Q1_1_Household_ID=="223"| Q1_1_Household_ID=="239"| Q1_1_Household_ID=="261"| Q1_1_Household_ID=="282"| Q1_1_Household_ID=="288"| Q1_1_Household_ID=="306"| Q1_1_Household_ID=="317"| Q1_1_Household_ID=="320"| Q1_1_Household_ID=="365"| Q1_1_Household_ID=="400"| Q1_1_Household_ID=="408"| Q1_1_Household_ID=="409"
replace roundtwo=1 if roundtwo==.

gen protcostperton=Q2_53_costofprotection*1000/(2100*amountstored)
replace Q2_60_highest_price=Q2_60_highest_price*1000/2100
replace Q2_61_lowest_price=Q2_61_lowest_price*1000/2100
gen averageprice=0.5*(Q2_60_highest_price+Q2_61_lowest_price)

replace Q5_11_Infestation=0 if Q5_11_Infestation==3
replace Q5_11_Infestation=3 if Q5_11_Infestation==1
replace Q5_11_Infestation=1 if Q5_11_Infestation==0
replace Q5_12_Rotting=0 if Q5_12_Rotting==3
replace Q5_12_Rotting=3 if Q5_12_Rotting==1
replace Q5_12_Rotting=1 if Q5_12_Rotting==0
label define Q5_11_Infestation 3 "Increased greatly" 2 "Increased" 1 "Remained the same", replace
label define Q5_12_Rotting 3 "Increased greatly" 2 "Increased" 1 "Remained the same", replace
pca Q5_7_Size_shape Q5_8_Aroma Q5_9_Taste Q5_10_Colour Q5_11_Infestation Q5_12_Rotting
predict qualityindex
gen est_sample=(qualityindex!=.)
gen wave=1

rename Q1_1_Household_ID hhid
rename Q2_1A_agriclandprev agriclandarea
rename Q2_2A_maizelandprev maizelandarea
rename Q2_3A_maizeplotsprev maizeplots
rename Q2_17_harvest_maturity harvestmatured
rename Q2_26_immediatestorage immediatestorage
rename Q2_28_maize_sorted maizesorted
rename Q2_38_drying_period dryingperiod
rename Q2_9_actual_harvest amountharvested
rename Q2_47_clean_storage storecleaned 
rename Q2_49_disinfect_storage storedisinfected
rename Q2_51_protected_stored_product usedprotectants
rename Q2_52A_used_chemicals Spraying
rename Q2_52B_used_ashesandsmoking Smoking
rename Q2_52C_used_rattrapsandpoison Trappoison
rename Q2_52D_used_cats cats
rename Q2_57_sell_harvest soldmaize
rename Q2_58_amont_sold amountsold
rename Q2_59_sold3months amountsoldin3months
rename Q2_60_highest_price highestprice
rename Q2_61_lowest_price lowestprice
rename Q2_62_transactions transactions
rename Q2_63_transport4sale transported4sale

rename Q5_7_Size_shape size_shape
rename Q5_8_Aroma aroma
rename Q5_9_Taste taste
rename Q5_10_Colour colour
rename Q5_11_Infestation infestation
rename Q5_12_Rotting rotting

merge 1:1 hhid using "PHL First Round Socieoeconomic.dta"
drop _merge
save "Second round data analysed.dta", replace

keep hhid roundtwo est_sample
saveold "PHL Second  Round estimation sample.dta", replace

use "Drivers of post harvest lossses Final.dta", clear
merge 1:1 hhid using "PHL Second  Round estimation sample.dta"
drop _merge
saveold "First Round Data.dta", replace


use "Second round data analysed.dta", clear
append using "First Round data.dta"
pca harvestmatured immediatestorage maizesorted dryingperiod storedisinfected 
predict managpractindex

keep hhid villagecode control bagstreatment traintreatment wave prestolossprop stolossprop selllossprop phl agriclandarea maizeplots maizelandarea weatheratharvest harvestmatured immediatestorage maizesorted dryingperiod amountharvested amountstored protcostperton storedisinfected usedprotectants soldmaize amountsold amountsoldin3months propsoldin3months Spraying Trappoison protcostperton highestprice lowestprice averageprice sex age schooling activeworkers hhsize income wealth maizeexperienceyears trained phl size_shape aroma taste colour infestation rotting qualityindex managpractindex est_sample
sort wave hhid

order hhid villagecode control bagstreatment traintreatment wave sex age schooling activeworkers hhsize income wealth maizeexperienceyears trained agriclandarea maizelandarea maizeplots amountharvested amountstored soldmaize amountsold amountsoldin3months propsoldin3months highestprice lowestprice averageprice weatheratharvest harvestmatured immediatestorage maizesorted dryingperiod storedisinfected usedprotectants Spraying Trappoison protcostperton prestolossprop stolossprop selllossprop phl size_shape aroma taste colour infestation rotting qualityindex managpractindex est_sample

label define yes_no 1 "Yes" 0 "No"
label values control yes_no
label values bagstreatment yes_no
label values traintreatment yes_no
label define wave 1 "second wave" 0 "first wave"
label values wave wave
label define weather 1 "sunny" 0 "humid"
label values weatheratharvest weather


lab var control "control village"
lab var bagstreatment "village receiving hermetic bags and training treatment"
lab var traintreatment "village receiving training only treatment"
lab var wave "survey wave"
lab var schooling "years of schooling"
lab var income "annual earnings from different income sources by houseshold in USD"
lab var wealth "value of assets in USD"
lab var activeworkers "number of household members actively involved in production activities"
lab var hhsize "household size"
lab var phl "total post harvest losses in three stages as proportion of amount harvested "
lab var prestolossprop "pre storage losses as proportion of amount harvested "
lab var stolossprop "storage losses as proportion of amount harvested "
lab var stolossprop "storage losses as proportion of amount harvested "
lab var selllossprop "marketing losses as proportion of amount harvested "
lab var agriclandarea "land used for agriculture in the previous season in hectares"
lab var maizelandarea "land used for maize production in the previous season in hectares"
lab var weatheratharvest "dummy for sunny weather at harvest"
lab var harvestmatured "dummy for maize harvested immediately after maturity"
lab var amountharvested "amount of maize harvested in kgs"
lab var immediatestorage "dummy for proper immediate handling after harvest"
lab var dryingperiod "number of day maize was dried after harvesting"
lab var amountstored "amount of maize stored in kgs"
lab var propsoldin3months "proportion of maize sold within three months"
lab var averageprice "simple mean price of highest and lowest price recived by a famer"
lab var protcostperton "cost of protecting store crop against post harvet losses"
lab var qualityindex "score for principal component analysis of maize quality attributes"
lab var managpractindex "score for principal component analysis of post harvest management practices adopted"
lab var est_sample "a dummy for observation with all they key variables for estimation"

save "CES2019_data.dta", replace


